using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Buecherverleih;
using MySql.Data.MySqlClient;
using System.Diagnostics;




namespace Buecherverleih
{
    public partial class Form1 : Form
    {
        //Klassen / Globale Variablen
        string myConnectionStringGlobal = "SERVER=10.110.0.250;" +
                                "DATABASE=dbinventar;" +
                                "UID=dbinventar;" +
                                "PASSWORD=nX4NuFrh8EDPVM5X;";
        string version = "1.12";

        Panel BringMeToFront;
        Panel FocusMe;


        public Form1()
        {
            InitializeComponent();
        }


         /* 
          *  Insert Beispiel
          * 
          * MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
            string myInsertQuery = "INSERT INTO dbbv_borrowers Values(1, 'Patrick Michiels')";
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
            myCommand.Connection = myConnection;
            myConnection.Open();
            myCommand.ExecuteNonQuery();
            myCommand.Connection.Close();  
          
          
          */

        //Beim Start des Programms wird dies ausgeführt
        private void Form1_Load(object sender, EventArgs e)
        {
            if (HasNewVersion())
            {
                MessageBox.Show("Es wird nun ein Update heruntergeladen!\n\n" +
                            "Ihre aktuelle Version: " + version,
                            "Update erforderlich!",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Information);


                Process myProcess = new Process();

                try
                {
                    myProcess.StartInfo.UseShellExecute = false;
                    // You can start any process, HelloWorld is a do-nothing example.
                    myProcess.StartInfo.FileName = "Buecherupdate.exe";
                    myProcess.StartInfo.CreateNoWindow = true;
                    myProcess.Start();
                    // This code assumes the process you are starting will terminate itself. 
                    // Given that is is started without a window so you cannot terminate it 
                    // on the desktop, it must terminate itself or you can do it programmatically
                    // from this application using the Kill method.
                }
                catch
                {
                       MessageBox.Show("Konnte kein Update machen )=\n"+
                           "Bitte kopieren Sie sich die 'Buecherupdate.exe' "+
                           "mit in den Ordner, in der IHRE 'Buecherverleih.exe' liegt!\n"+
                           "Sie finden diese unter '//10.110.0.31/austausch/Ausbildung/FI/Buecherverleih/hier_ist_das_programm/Buecherverleih'",
                           "WARNING!",
                           MessageBoxButtons.OK,
                           MessageBoxIcon.Warning);
                }

            }


            pnl_login.BringToFront();
            pnl_login.Focus();
        }

        private void btn_showAll_Home_Click(object sender, EventArgs e)
        {
            pnl_Search_All.BringToFront();
            alleBuecherAnzeigen(0);
            erstelleBuecherMenue();
        }

        private void btn_showAll_main_Click(object sender, EventArgs e)
        {
                pnl_Search_All.BringToFront();
                alleBuecherAnzeigen(0);
                erstelleBuecherMenue();

            pnl_Search_All_Rows.Focus();
        }

        private void btn_Home_main_Click(object sender, EventArgs e)
        {
            pnl_Home.BringToFront();
        }

        private void btn_newBorrower_Click(object sender, EventArgs e)
        {
            pnl_newBorrower.BringToFront();
        }

        //Speichernbutton auf der newborrower Maske
        private void btn_newBorrower_save_Click(object sender, EventArgs e)
        {
            //Überprüfen, ob in beide Textfelder was eingetragen wurde
            if (tbox_newBorrower_Firstname.Text == "" || tbox_newBorrower_Lastname.Text == "")
            {
                //Fehlermeldung wenn nicht
                MessageBox.Show("Tragen Sie bitte in beide Textfelder etwas ein!",
                    "Warnung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                DialogResult result = MessageBox.Show("Benutzer anlegen?\n\nVorname: " +
                    tbox_newBorrower_Firstname.Text + "\nNachname: " +
                    tbox_newBorrower_Lastname.Text,
                    "Speicherbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                    string myInsertQuery = "INSERT INTO dbbv_borrowers (borrower_familyname, borrower_firstname) Values('" +
                        tbox_newBorrower_Lastname.Text + "', '" + tbox_newBorrower_Firstname.Text + "')";
                    MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                    myCommand.Connection = myConnection;
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myCommand.Connection.Close();

                    MessageBox.Show("Benutzer: " + tbox_newBorrower_Lastname.Text +
                        ", " + tbox_newBorrower_Firstname.Text + " angelegt!",
                        "Speicherbestätigung!",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                    borrowerManagement_start();
                }

            }
        }

        private void btn_borrowerManagement_Click(object sender, EventArgs e)
        {
            borrowerManagement_start();
        }

        public int countAllBorrowers()
        {
            int allBorrowersCounted = 0;
            //Verbindung zur Datenbank aufbauen
            //Methode mit Rückgabewert Alle Bücher zählen aufrufen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM dbbv_borrowers";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                allBorrowersCounted = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            return allBorrowersCounted;
        }

        public int countAllUsers()
        {
            int allUsersCounted = 0;
            //Verbindung zur Datenbank aufbauen
            //Methode mit Rückgabewert Alle Bücher zählen aufrufen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM dbbv_users";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                allUsersCounted = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            return allUsersCounted;
        }


        public int countAllBooks()
        {
            int allBooksCounted = 0;
            //Verbindung zur Datenbank aufbauen
            //Methode mit Rückgabewert Alle Bücher zählen aufrufen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT COUNT(*) FROM dbbv_books";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                allBooksCounted = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            return allBooksCounted;
        }

        private void btn_borrowerEdit_Cancel_Click(object sender, EventArgs e)
        {
            pnl_borrowerManagement.BringToFront();
            pnl_borrowerManagement_main.Focus();
        }

        public void borrower_bearbeiten_Click(object sender, System.EventArgs e)
        {
            int borrowerID = 0;
            pnl_borrower_edit.BringToFront();

            borrowerID = Convert.ToInt32(((Button)sender).Name);

            tBox_borrowerEdit_ID.Text = borrowerID.ToString();

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_borrowers WHERE ID = " + borrowerID;
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                tBox_borrowerEdit_Lastname.Text = Reader.GetValue(1).ToString();
                lbl_oldBorrowerNachname.Text = Reader.GetValue(1).ToString();
                tBox_borrowerEdit_Firstname.Text = Reader.GetValue(2).ToString();
                lbl_oldBorrowerVorname.Text = Reader.GetValue(2).ToString();
            }
            connection.Close();
        }


        public void user_bearbeiten_Click(object sender, System.EventArgs e)
        {
            int userID = 0;
            pnl_User_edit.BringToFront();

            userID = Convert.ToInt32(((Button)sender).Name);

            tbox_UserEdit_ID.Text = userID.ToString();

            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_users WHERE ID = " + userID;
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                tbox_UserEdit_Nachname.Text = Reader.GetValue(2).ToString();
                lbl_UserEdit_OldNachname.Text = Reader.GetValue(2).ToString();
                tbox_UserEdit_Vorname.Text = Reader.GetValue(3).ToString();
                lbl_UserEdit_OldVorname.Text = Reader.GetValue(3).ToString();
                tbox_UserEdit_username.Text = Reader.GetValue(1).ToString();
                //Man darf sich nicht selbst das Passwort zurücksetzen
                if (tbox_UserEdit_username.Text == tbox_username.Text)
                {
                    btn_resetPW.Visible = false;
                }
                else
                {
                    btn_resetPW.Visible = true;
                }
                //Falls User == Admin
                if (Reader.GetValue(5).ToString().ToLower() == "true")
                {
                    btn_makeAdmin.Visible = false;
                }
                else
                {
                    btn_makeAdmin.Visible = true;
                }
            }
            connection.Close();
        }


        private void btn_borrowerEdit_Save_Click(object sender, EventArgs e)
        {
            if (tBox_borrowerEdit_Lastname.Text == lbl_oldBorrowerNachname.Text &&
                tBox_borrowerEdit_Firstname.Text == lbl_oldBorrowerVorname.Text)
            {
                MessageBox.Show("Sie haben keine Änderungen vorgenommen!",
                    "Information!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myInsertQuery = "UPDATE dbbv_borrowers SET borrower_familyname='" + tBox_borrowerEdit_Lastname.Text +
                    "', borrower_firstname='" + tBox_borrowerEdit_Firstname.Text + "' WHERE ID = " +
                    tBox_borrowerEdit_ID.Text;
                MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                MessageBox.Show("Benutzer: " + tBox_borrowerEdit_Lastname.Text +
                    ", " + tBox_borrowerEdit_Firstname.Text + " gespeichert!",
                    "Speicherbestätigung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);

                borrowerManagement_start();
            }
        }

        //Löschen Button der Ausleiher bearbeiten Maske
        private void btn_borrowerEdit_Erase_Click(object sender, EventArgs e)
        {
            Boolean loeschOK = true;
            string ausgelieheneBuecher = "";

            MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command2 = connection2.CreateCommand();
            command2.CommandText = "SELECT labelID FROM dbbv_books WHERE borrowed_by = " +
                tBox_borrowerEdit_ID.Text;
            MySqlDataReader Reader2;
            connection2.Open();
            Reader2 = command2.ExecuteReader();
            while (Reader2.Read())
            {
                ausgelieheneBuecher += Reader2.GetValue(0).ToString() + "\n";
                loeschOK = false;
            }
            connection2.Close();


            if (loeschOK)
            {
                DialogResult result = MessageBox.Show("Benutzer löschen?\n\nVorname: " +
                    tBox_borrowerEdit_Firstname.Text + "\nNachname: " +
                    tBox_borrowerEdit_Lastname.Text,
                    "Löschbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                    string myDeleteQuery = "DELETE FROM dbbv_borrowers WHERE ID = " +
                        tBox_borrowerEdit_ID.Text;
                    MySqlCommand myCommand = new MySqlCommand(myDeleteQuery);
                    myCommand.Connection = myConnection;
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myCommand.Connection.Close();

                    MessageBox.Show("Benutzer: " + tBox_borrowerEdit_Lastname.Text +
                        ", " + tBox_borrowerEdit_Firstname.Text + " gelöscht!",
                        "Löschbestätigung!",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                    borrowerManagement_start();
                }
            }
            else
            {
                MessageBox.Show("Der Benutzer kann nicht gelöscht werden, " +
                    "da folgende Buecher von diesem ausgeliehen werden:\n\n" +
                    "LabelID(s):\n" + ausgelieheneBuecher,
                    "ERROR",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
        }

        private void btn_newBorrower_Click_2(object sender, EventArgs e)
        {
            tbox_newBorrower_Firstname.Text = "";
            tbox_newBorrower_Lastname.Text = "";
            pnl_newBorrower.BringToFront();
        }

        //wenn bei cBox_Nachname die Auswahl sich ändert wird diese Funktion ausgeführt
        private void cBox_Nachname_SelectedIndexChanged(object sender, EventArgs e)
        {
            //auf die textbox den Vornamen schreiben und vorher aus DB holen

            //Variable Nachname des selected Items und Variable für Anzahl des Vorkommens dieses
            string Nachname = cBox_Nachname.SelectedItem.ToString();
            int amountNachnamen = 0;

            //Count wie viele mit diesem Nachnamen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = " SELECT Count(*) FROM dbbv_borrowers WHERE borrower_familyname = '" +
                Nachname + "'";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                amountNachnamen = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            if (amountNachnamen == 1)
            {
                MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command2 = connection2.CreateCommand();
                command2.CommandText = " SELECT borrower_firstname FROM dbbv_borrowers WHERE borrower_familyname = '" +
                    Nachname + "'";
                MySqlDataReader Reader2;
                connection2.Open();
                Reader2 = command2.ExecuteReader();
                while (Reader2.Read())
                {
                    tBox_Vorname.Text = Reader2.GetValue(0).ToString();
                }
                connection2.Close();
            }
            else
            {
                pnl_borrow_selectVorname.BringToFront();

                //Vermeiden von Mehrfachbeschreibung
                cBox_Vorname.Items.Clear();

                MySqlConnection connection3 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command3 = connection3.CreateCommand();
                command3.CommandText = " SELECT borrower_firstname FROM dbbv_borrowers WHERE borrower_familyname = '" +
                    Nachname + "'";
                MySqlDataReader Reader3;
                connection3.Open();
                Reader3 = command3.ExecuteReader();
                while (Reader3.Read())
                {
                    cBox_Vorname.Items.Add(Reader3.GetValue(0).ToString());
                }
                connection3.Close();
            }
        }

        private void cBox_Vorname_SelectedIndexChanged(object sender, EventArgs e)
        {
            pnl_borrow.BringToFront();
            tBox_Vorname.Text = cBox_Vorname.SelectedItem.ToString();
        }

        private void btn_borrowerMangement_main_Click(object sender, EventArgs e)
        {
            borrowerManagement_start();
        }

        private void btn_newBorrower_cancel_Click(object sender, EventArgs e)
        {
            pnl_borrowerManagement.BringToFront();
            pnl_borrowerManagement_main.Focus();
        }


        private void btn_newBook_main_Click(object sender, EventArgs e)
        {
            pnl_newbook.BringToFront();
        }

        private void btn_newBook_Cancel_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == "" &&
                textBox2.Text == "" &&
                cbox_booktype_new.Text == "" &&
                textBox4.Text == "" &&
                textBox6.Text == "" &&
                cbox_book_location_new.Text == "" &&
                richTextBox1.Text == "")
            {
                pnl_Home.BringToFront();
            }
            else
            {
                DialogResult result = MessageBox.Show("Abbrechen und zurück zum Home-Screen?",
                "Abbrechbestätigung",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question);

                if (result == DialogResult.OK)
                {
                    pnl_Home.BringToFront();
                }
            }
        }

        private void btn_newBook_Home_Click(object sender, EventArgs e)
        {
            pnl_newbook.BringToFront();
            fill_cbox_booktype_new();
            fill_cbox_book_location_new();

            //Bei Aufruf des Panels alle Felder leeren
            textBox1.Text = "";
            textBox2.Text = "";
            textBox4.Text = "";
            textBox6.Text = "";
            richTextBox1.Text = "";
        }

        private void btn_newBook_main_Click_1(object sender, EventArgs e)
        {
            pnl_newbook.BringToFront();
            fill_cbox_booktype_new();
            fill_cbox_book_location_new();

            //Bei Aufruf des Panels alle Felder leeren
            textBox1.Text = "";
            textBox2.Text = "";
            textBox4.Text = "";
            textBox6.Text = "";
            richTextBox1.Text = "";
        }

        private void btn_borrowerManagement_Home_Click(object sender, EventArgs e)
        {
            borrowerManagement_start();
        }

        private void btn_book_Back_Click(object sender, EventArgs e)
        {
            BringMeToFront.BringToFront();
            FocusMe.Focus();
        }

        //Button Buch verleihen
        private void btn_BuchVerleihen_Click(object sender, EventArgs e)
        {
            // Prüfen, ob Ausleiher eingetragen
            if (cBox_Nachname.Text == "")
            {
                MessageBox.Show("Bitte Ausleiher auswählen!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                DialogResult result = MessageBox.Show("Möchten Sie dieses Buch ausleihen?\n\nBuchname: " +
                    tbox_DB_bookname.Text + "\nLabel_ID: " +
                    tbox_DB_labelID.Text, "Bestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    string borrowerID = "";
                    // Rausfinden welche ID hinter Nachname und Vorname steckt
                    MySqlConnection connection3 = new MySqlConnection(myConnectionStringGlobal);
                    MySqlCommand command3 = connection3.CreateCommand();
                    command3.CommandText = "SELECT ID FROM dbbv_borrowers WHERE borrower_familyname = '" +
                        cBox_Nachname.Text + "' AND borrower_firstname = '" + tBox_Vorname.Text + "'";
                    MySqlDataReader Reader3;
                    connection3.Open();
                    Reader3 = command3.ExecuteReader();
                    while (Reader3.Read())
                    {
                        borrowerID = Reader3.GetValue(0).ToString();
                    }
                    connection3.Close();

                    //zurückschreiben der Änderungen in Datenbank
                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                    string myDeleteQuery = "UPDATE dbbv_books SET isborrowed = 1, " +
                        "borrowed_by = " + borrowerID +
                        ", borrowed_on = '" + dt_Picker_borrowed_on.Value.Year.ToString() + 
                        "-" + dt_Picker_borrowed_on.Value.Month.ToString() + 
                        "-" + dt_Picker_borrowed_on.Value.Day.ToString() + "' " +
                        ", reserved = 0 " +
                        "WHERE ID = " + lbl_DB_ID.Text;
                    MySqlCommand myCommand = new MySqlCommand(myDeleteQuery);
                    myCommand.Connection = myConnection;
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myCommand.Connection.Close();

                    // Bestätigung an Benutzer
                    MessageBox.Show("Buch wurde ausgeliehen!", 
                        "Speicherbestätigung",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                    pnl_Search_All.BringToFront();

                    //mit vorher aufgerufenem Parameter wieder aufrufen
                    alleBuecherAnzeigen(Convert.ToInt32(lbl_restartNumberCallBookList.Text));
                    erstelleBuecherMenue();
                }
            }
        }

        //Speichern Button eine neuen Buches
        private void btn_newBook_Save_Click(object sender, EventArgs e)
        {
            //Überprüfen, ob in alle Felder etwas eingetragen wurde
            if (textBox1.Text == "" ||
                textBox2.Text == "" ||
                cbox_booktype_new.Text == "" ||
                textBox4.Text == "" ||
                textBox6.Text == "" ||
                cbox_book_location_new.Text == "" ||
                richTextBox1.Text == "")
            {
                //Warnmeldung
                MessageBox.Show("Bitte geben Sie in alle Felder etwas ein!",
                    "Warning",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            //Label ID muss unique sein!
            else
            {
                if (!checkLabel_isUnique())
                {
                    //Warnmeldung
                    MessageBox.Show("Diese LabelID ist bereits in Verwendung!\n" +
                        "Bitte wählen Sie eine einzigartige LabelID!",
                        "Warning",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Warning);
                }
                else
                {
                    DialogResult result = MessageBox.Show("Sind alle Eingaben korrekt?",
                        "Speicherbestätigung",
                        MessageBoxButtons.OKCancel,
                        MessageBoxIcon.Question);

                    if (result == System.Windows.Forms.DialogResult.OK)
                    {
                        MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                        string myInsertQuery = "INSERT INTO dbbv_books (labelID, " +
                            "name, content, type, publisher, theme, location, isborrowed)" +
                            "VALUES ('" + textBox2.Text + "'," +
                            "'" + textBox1.Text + "'," +
                            "'" + richTextBox1.Text + "'," +
                            "'" + get_ID_Type(cbox_booktype_new.Text) + "'," +
                            "'" + textBox4.Text + "'," +
                            "'" + textBox6.Text + "'," +
                            "'" + get_ID_Ort(cbox_book_location_new.Text) + "'," +
                            "0)";

                        MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                        myCommand.Connection = myConnection;
                        myConnection.Open();
                        myCommand.ExecuteNonQuery();
                        myCommand.Connection.Close();


                        MessageBox.Show("Neues Buch angelegt!");


                        //Bücherliste anzeigen
                        pnl_Search_All.BringToFront();
                        alleBuecherAnzeigen(0);
                        erstelleBuecherMenue();
                    }
                }
            }
        }

        private void btn_eraseBook_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("Moechten Sie dieses Buch wirklich löschen?",
                    "Löschbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

            if (result == System.Windows.Forms.DialogResult.OK)
            {
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myDeleteQuery = "DELETE FROM dbbv_books " +
                    "WHERE ID=" + tbox_book_ID.Text;

                MySqlCommand myCommand = new MySqlCommand(myDeleteQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                //Löschbestätigung
                MessageBox.Show("Buch wurde geloescht!");

                //Bücherliste anzeigen
                pnl_Search_All.BringToFront();
                alleBuecherAnzeigen(0);
                erstelleBuecherMenue();
            }
        }

        private void btn_searchPersons_Click(object sender, EventArgs e)
        {
            //Vorname leeren
            tbox_Vorname_Persons.Text = "";

            //Auflistung der Combobox hinzufügen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT borrower_familyname FROM dbbv_borrowers";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            cbox_Surname_Search.Items.Clear();

            while (Reader.Read())
            {
                cbox_Surname_Search.Items.Add(Reader.GetValue(0).ToString());
            }
            connection.Close();

            //Panel der Personensuche nach vorne holen
            pnl_searchPersons.BringToFront();
            pnl_SearchPersons_Main.Focus();
        }


        //wenn bei cbox_Surname_Search die Auswahl sich ändert wird diese Funktion ausgeführt
        private void cbox_Surname_Search_SelectedIndexChanged(object sender, EventArgs e)
        {
            //auf die textbox den Vornamen schreiben und vorher aus DB holen

            //Variable Nachname des selected Items und Variable für Anzahl des Vorkommens dieses
            string Nachname = cbox_Surname_Search.SelectedItem.ToString();
            int amountNachnamen = 0;

            //Count wie viele mit diesem Nachnamen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = " SELECT Count(*) FROM dbbv_borrowers WHERE borrower_familyname = '" +
                Nachname + "'";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            while (Reader.Read())
            {
                amountNachnamen = Convert.ToInt32(Reader.GetValue(0).ToString());
            }
            connection.Close();

            if (amountNachnamen == 1)
            {
                MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command2 = connection2.CreateCommand();
                command2.CommandText = " SELECT borrower_firstname FROM dbbv_borrowers WHERE borrower_familyname = '" +
                    Nachname + "'";
                MySqlDataReader Reader2;
                connection2.Open();
                Reader2 = command2.ExecuteReader();
                while (Reader2.Read())
                {
                    tbox_Vorname_Persons.Text = Reader2.GetValue(0).ToString();
                }
                connection2.Close();
            }
            else
            {
                pnl_selectVorname_Search.BringToFront();

                //Vermeiden von Mehrfachbeschreibung
                cbox_Vorname_Search.Items.Clear();

                MySqlConnection connection3 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command3 = connection3.CreateCommand();
                command3.CommandText = " SELECT borrower_firstname FROM dbbv_borrowers WHERE borrower_familyname = '" +
                    Nachname + "'";
                MySqlDataReader Reader3;
                connection3.Open();
                Reader3 = command3.ExecuteReader();
                while (Reader3.Read())
                {
                    cbox_Vorname_Search.Items.Add(Reader3.GetValue(0).ToString());
                }
                connection3.Close();
            }
        }

        private void btn_search_main_Click(object sender, EventArgs e)
        {
            pnl_searchHome.BringToFront();
        }

        private void btn_search_Home_Click(object sender, EventArgs e)
        {
            pnl_searchHome.BringToFront();
        }

        private void btn_searchLabelID_Click(object sender, EventArgs e)
        {

            //Auflistung der Combobox hinzufügen
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT labelID FROM dbbv_books";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            cbox_search_LabelID.Items.Clear();

            while (Reader.Read())
            {
                cbox_search_LabelID.Items.Add(Reader.GetValue(0).ToString());
            }
            connection.Close();


            pnl_search_Label.BringToFront();
            pnl_Search_Label_Main.Focus();
        }

        //Button, der die Suche nach der (hoffentlich) ausgewählten Person started
        private void btn_searchGo_Persons_Click(object sender, EventArgs e)
        {
            // Prüfen, ob Person eingetragen
            if (cbox_Surname_Search.Text == "")
            {
                MessageBox.Show("Bitte Person auswählen!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                //Panel nach vorne
                pnl_SearchPersons_Super.BringToFront();

                //Alle Bücher anzeigen, die von Person XY ausgeliehen wurden
                alleBuecherAnzeigen_ausgeliehenVonPerson(cbox_Surname_Search.Text,tbox_Vorname_Persons.Text);

                //Für Mausscrollen Main Panel fokussieren
                pnl_SearchPersons_Main.Focus();
            }
        }



        //Suchen Button, der nach Label-ID suchen lässt
        private void btn_searchLabel_Home_Click(object sender, EventArgs e)
        {
            // Prüfen, ob Label eingetragen
            if (cbox_search_LabelID.Text == "")
            {
                MessageBox.Show("Bitte LabelID eintragen!", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            else
            {
                //Panel nach vorne
                pnl_Search_Label_Super.BringToFront();

                //Das ausgewählte Buch anzeigen
                show_book_LabelID(cbox_search_LabelID.Text);

                //Für Mausscrollen Main Panel fokussieren
                pnl_Search_Label_Main.Focus();
            }
        }

        //Button, der aktuell alle ausgeliehenen Bücher anzeigt
        private void btn_searchAllLendBooks_Click(object sender, EventArgs e)
        {
            //Panel nach vorne
            pnl_searchAllLend.BringToFront();

            //Alle ausgewählten Bücher anzeigen
            showAllLendBooks(0);

            //Menü erstellen
            createAllLendBooksMenu();

            //Für Mausscrollen Main Panel fokussieren
            pnl_searchAllLend_Main.Focus();
        }

        private void btn_login_Click(object sender, EventArgs e)
        {
            login();
        }

        private void tbox_username_KeyDown(object sender, KeyEventArgs e)
        {
            if(e.KeyCode == Keys.Enter)
            {
                login();
            }
        }

        private void tbox_password_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                login();
            }
        }

        
        private void btn_ChangeDefaultPW_Click(object sender, EventArgs e)
        {
            changedefaultpw();
        }

        private void tbox_ChangeDefaultpwPassword_KeyDown(object sender, KeyEventArgs e)
        {
            if(e.KeyCode == Keys.Enter)
            {
                changedefaultpw();
            }
        }

        private void tbox_ChangeDefaultpwconfirmpw_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                changedefaultpw();
            }
        }

        private void btn_options_main_Click(object sender, EventArgs e)
        {
            startOptions();
        }

        private void btn_options_Home_Click(object sender, EventArgs e)
        {
            startOptions();
        }

        private void btn_borrowerStart_Click(object sender, EventArgs e)
        {
            borrowerManagement_start();
        }

        private void btn_locationsStart_Click(object sender, EventArgs e)
        {
            startLocation();
        }

        private void btn_newLocation_Click(object sender, EventArgs e)
        {
            pnl_newLocation.BringToFront();
        }

        private void btn_CancelNewLocation_Click(object sender, EventArgs e)
        {
            pnl_locations_Super.BringToFront();
            pnl_locations_Main.Focus();
        }

        //Speichern Button eines neuen Aufbewahrungsortes 
        private void btn_saveNewLocation_Click(object sender, EventArgs e)
        {
            //Überprüfen, ob in das Textfeld was eingetragen wurde
            if (tbox_newLocationName.Text == "")
            {
                //Fehlermeldung wenn nicht
                MessageBox.Show("Tragen Sie bitte in das Textfeld etwas ein!",
                    "Warnung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                DialogResult result = MessageBox.Show("Aufbewahrungsort anlegen?\n\nName: " +
                    tbox_newLocationName.Text,
                    "Speicherbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                    string myInsertQuery = "INSERT INTO dbbv_ort (Ort) Values('" +
                        tbox_newLocationName.Text + "')";
                    MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                    myCommand.Connection = myConnection;
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myCommand.Connection.Close();

                    MessageBox.Show("Aufbewahrungsort: " 
                        + tbox_newLocationName.Text + 
                        " angelegt!",
                        "Speicherbestätigung!",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                    startLocation();
                }
            }
        }

        //Abbrechen Button bei Aufbewahrungsort bearbeiten
        private void btn_Cancel_editLocation_Click(object sender, EventArgs e)
        {
            pnl_locations_Super.BringToFront();
            pnl_locations_Main.Focus();
        }

        //Löschen Button bei Aufbewahrungsort bearbeiten
        private void btn_Delete_editLocation_Click(object sender, EventArgs e)
        {
            Boolean loeschOK = true;
            string buecherMitAufbewahrungsort = "";
            int counter = 0;

            MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command2 = connection2.CreateCommand();
            command2.CommandText = "SELECT labelID FROM dbbv_books WHERE location = " +
                tbox_locationID.Text;
            MySqlDataReader Reader2;
            connection2.Open();
            Reader2 = command2.ExecuteReader();
            while (Reader2.Read())
            {
                buecherMitAufbewahrungsort += Reader2.GetValue(0).ToString() + "\n";
                loeschOK = false;
                counter++;
            }
            connection2.Close();


            if (loeschOK)
            {
                DialogResult result = MessageBox.Show("Aufbewahrungsort löschen?\n\nName: " +
                    tbox_locationName.Text,
                    "Löschbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {

                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                    string myDeleteQuery = "DELETE FROM dbbv_ort WHERE ID = " +
                        tbox_locationID.Text;
                    MySqlCommand myCommand = new MySqlCommand(myDeleteQuery);
                    myCommand.Connection = myConnection;
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myCommand.Connection.Close();

                    MessageBox.Show("Aufbewahrungsort: " + tbox_locationName.Text
                        + " gelöscht!",
                        "Löschbestätigung!",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                    startLocation();
                }
            }
            else
            {
                if (counter > 10)
                {
                    MessageBox.Show("Der Aufbewahrungsort kann nicht gelöscht werden, " +
                        "da " + counter.ToString() + " Buecher an diesem Ort stehen!",
                        "ERROR",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Error);
                }
                else
                {
                    MessageBox.Show("Der Aufbewahrungsort kann nicht gelöscht werden, " +
                        "da " + counter.ToString() + " Buecher an diesem Ort stehen!\n\n" +
                        "LabelID(s):\n" + buecherMitAufbewahrungsort,
                        "ERROR",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Error);
                }
            }
        }

        //Speichern Button bei Aufbewahrungsort bearbeiten
        private void btn_Save_editLocation_Click(object sender, EventArgs e)
        {
            if (tbox_locationName.Text == lbl_oldlocationName.Text)
            {
                MessageBox.Show("Sie haben keine Änderungen vorgenommen!",
                    "Information!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myUpdateQuery = "UPDATE dbbv_ort SET Ort='" +
                    tbox_locationName.Text + 
                    "' WHERE ID = " +
                    tbox_locationID.Text;
                MySqlCommand myCommand = new MySqlCommand(myUpdateQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                MessageBox.Show("Aufbewahrungsort: " + tbox_locationName.Text + 
                    " gespeichert!",
                    "Speicherbestätigung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);

                startLocation();
            }
        }

        //Button auf Options, der Die Buchtypoptionen startet
        private void btn_booktypesStart_Click(object sender, EventArgs e)
        {
            startbooktypes();
        }

        //Neuen Buchtype anlegen
        private void btn_newBooktype_Click(object sender, EventArgs e)
        {
            pnl_newBooktype.BringToFront();
        }

        //Zurück zu den booktype Options
        private void btn_newBooktypeCancel_Click(object sender, EventArgs e)
        {
            pnl_booktype_Super.BringToFront();
            pnl_booktype_Main.Focus();
        }

        //Speichern eines neuen Buchtyps
        private void btn_newBooktypeSave_Click(object sender, EventArgs e)
        {
            //Überprüfen, ob in das Textfeld was eingetragen wurde
            if (tbox_newBooktypeName.Text == "")
            {
                //Fehlermeldung wenn nicht
                MessageBox.Show("Tragen Sie bitte in das Textfeld etwas ein!",
                    "Warnung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                DialogResult result = MessageBox.Show("Buchtyp anlegen?\n\nName: " +
                    tbox_newBooktypeName.Text,
                    "Speicherbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                    string myInsertQuery = "INSERT INTO dbbv_booktype (Type) Values('" +
                        tbox_newBooktypeName.Text + "')";
                    MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                    myCommand.Connection = myConnection;
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myCommand.Connection.Close();

                    MessageBox.Show("Buchtyp: "
                        + tbox_newBooktypeName.Text +
                        " angelegt!",
                        "Speicherbestätigung!",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                    startbooktypes();
                }
            }
        }


        //Abbrechen und zurück auf booktype_Super
        private void btn_booktypeEditCancel_Click(object sender, EventArgs e)
        {
            pnl_booktype_Super.BringToFront();
            pnl_booktype_Main.Focus();
        }


        //Löschen eines Buchtyps
        private void btn_booktypeEditDelete_Click(object sender, EventArgs e)
        {
            string buecherMitBuchtyp = "";
            int counter = 0;

            MySqlConnection connection2 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command2 = connection2.CreateCommand();
            command2.CommandText = "SELECT labelID FROM dbbv_books WHERE type = " +
                tbox_booktypeID.Text;
            MySqlDataReader Reader2;
            connection2.Open();
            Reader2 = command2.ExecuteReader();
            while (Reader2.Read())
            {
                buecherMitBuchtyp += Reader2.GetValue(0).ToString() + "\n";
                counter++;
            }
            connection2.Close();


            if (counter == 0)
            {
                DialogResult result = MessageBox.Show("Buchtyp löschen?\n\nName: " +
                    tbox_booktypeName.Text,
                    "Löschbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                    string myDeleteQuery = "DELETE FROM dbbv_booktype WHERE ID = " +
                        tbox_booktypeID.Text;
                    MySqlCommand myCommand = new MySqlCommand(myDeleteQuery);
                    myCommand.Connection = myConnection;
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    myCommand.Connection.Close();

                    MessageBox.Show("Buchtyp: " + tbox_booktypeName.Text
                        + " gelöscht!",
                        "Löschbestätigung!",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                    startbooktypes();
                }
            }
            else
            {
                if (counter > 10)
                {
                    MessageBox.Show("Der Buchtyp kann nicht gelöscht werden, " +
                        "da " + counter.ToString() + " Buecher diesen Buchtyp haben!",
                        "ERROR",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Error);
                }
                else
                {
                    MessageBox.Show("Der Buchtyp kann nicht gelöscht werden, " +
                        "da " + counter.ToString() + " Buecher diesen Buchtyp haben!\n\n" +
                        "LabelID(s):\n" + buecherMitBuchtyp,
                        "ERROR",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Error);
                }
            }
        }

        //Speichern von Änderungen am Buchtyp
        private void btn_booktypeEditSave_Click(object sender, EventArgs e)
        {
            if (tbox_booktypeName.Text == lbl_oldbooktypeName.Text)
            {
                MessageBox.Show("Sie haben keine Änderungen vorgenommen!",
                    "Information!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myUpdateQuery = "UPDATE dbbv_booktype SET Type='" +
                    tbox_booktypeName.Text +
                    "' WHERE ID = " +
                    tbox_booktypeID.Text;
                MySqlCommand myCommand = new MySqlCommand(myUpdateQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                MessageBox.Show("Buchtyp: " + tbox_booktypeName.Text +
                    " gespeichert!",
                    "Speicherbestätigung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);

                startbooktypes();
            }
        }

        //Button, der das Panel zum Ändern des Passworts aufruft
        private void btn_changePassword_Click(object sender, EventArgs e)
        {
            tbox_ChangePW_Username.Text = tbox_username.Text;
            pnl_changePassword.BringToFront();
            tbox_ChangePW_OldPW.Focus();
        }

        //Button zum Ändern des Passworts
        private void btn_ChangePW_Click(object sender, EventArgs e)
        {
            ChangePw();
        }

        private void tbox_ChangePW_OldPW_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                ChangePw();
            }
        }

        private void tbox_changePW_NewPW_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                ChangePw();
            }
        }

        private void tbox_ChangePW_ConfirmNewPW_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                ChangePw();
            }
        }

        private void btn_usersStart_Click(object sender, EventArgs e)
        {
            pnl_usersAdmin_Super.BringToFront();
            int allUsersAmount = countAllUsers();

            pnl_usersAdmin_Main.Controls.Clear();
            MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM dbbv_users";
            MySqlDataReader Reader;
            connection.Open();
            Reader = command.ExecuteReader();
            int counter = 0;
            Label[] Labels = new Label[(allUsersAmount * 3)];
            Button[] Buttons = new Button[(allUsersAmount)];

            while (Reader.Read())
            {
                Labels[(counter * 3)] = new Label();
                Labels[(counter * 3)].Left = 19;
                Labels[(counter * 3)].Top = 26 + (36 * counter);
                Labels[(counter * 3)].Width = 115;
                Labels[(counter * 3)].Height = 23;
                Labels[(counter * 3)].Text = Reader.GetValue(1).ToString();

                Labels[(1 + (counter * 3))] = new Label();
                Labels[(1 + (counter * 3))].Left = 142;
                Labels[(1 + (counter * 3))].Top = 26 + (36 * counter);
                Labels[(1 + (counter * 3))].Width = 160;
                Labels[(1 + (counter * 3))].Height = 23;
                Labels[(1 + (counter * 3))].Text = Reader.GetValue(3).ToString();

                Labels[(2 + (counter * 3))] = new Label();
                Labels[(2 + (counter * 3))].Left = 310;
                Labels[(2 + (counter * 3))].Top = 26 + (36 * counter);
                Labels[(2 + (counter * 3))].Width = 160;
                Labels[(2 + (counter * 3))].Height = 23;
                Labels[(2 + (counter * 3))].Text = Reader.GetValue(2).ToString();

                Buttons[(counter)] = new Button();
                Buttons[(counter)].Left = 477;
                Buttons[(counter)].Top = 21 + (36 * counter);
                Buttons[(counter)].Width = 88;
                Buttons[(counter)].Height = 23;
                Buttons[(counter)].Text = "bearbeiten";
                Buttons[(counter)].Name = Reader.GetValue(0).ToString();
                Buttons[(counter)].Font = new Font(Buttons[(counter)].Font, FontStyle.Bold);
                Buttons[(counter)].Click += new EventHandler(user_bearbeiten_Click);


                pnl_usersAdmin_Main.Controls.Add(Labels[(counter * 3)]);
                pnl_usersAdmin_Main.Controls.Add(Labels[(1 + (counter * 3))]);
                pnl_usersAdmin_Main.Controls.Add(Labels[(2 + (counter * 3))]);
                pnl_usersAdmin_Main.Controls.Add(Buttons[counter]);

                counter++;
            }
            connection.Close();

            pnl_usersAdmin_Main.Focus();
        }

        private void btn_UserEdit_Cancel_Click(object sender, EventArgs e)
        {
            pnl_usersAdmin_Super.BringToFront();
            pnl_usersAdmin_Main.Focus();
        }

        private void btn_newUser_Click(object sender, EventArgs e)
        {
            pnl_newUser.BringToFront();
            tbox_newUser_Nachname.Focus();
        }

        private void btn_makeAdmin_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show("Dieser Benutzer wird nun über "+
                "ALLE Rechte zum Ändern, Erstellen und Löschen von Benutzern verfügen!\n"+
                "Benutzername: " + tbox_UserEdit_username.Text +
                "\nVorname     : " + tbox_UserEdit_Vorname.Text +
                "\nNachname    : " + tbox_UserEdit_Nachname.Text +
                "\n\n"+
                "Möchten Sie diesen Benutzer zum Administrator befördern?",
                "Bestätigung",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question);

            if (result == System.Windows.Forms.DialogResult.OK)
            {
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myUpdateQuery = "UPDATE dbbv_users SET admin=1 WHERE ID = " +
                    tbox_UserEdit_ID.Text;
                MySqlCommand myCommand = new MySqlCommand(myUpdateQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                MessageBox.Show("Benutzer: " + tbox_UserEdit_username.Text +
                    " ist nun Administrator!",
                    "Speicherbestätigung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);

                pnl_Home.BringToFront();
            }
        }

        private void btn_UserEdit_Delete_Click(object sender, EventArgs e)
        {
            //Sich nicht selbst löschen man darf
            if (tbox_UserEdit_username.Text == tbox_username.Text)
            {
                MessageBox.Show("Sie können sich nicht selbst löschen!",
                    "ERROR",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }
            else
            {
                DialogResult result = MessageBox.Show("Möchten Sie diesen Benutzer löschen?"+
                    "\n\nBenutzername: " + tbox_UserEdit_username.Text,
                    "Löschbestätigung",
                    MessageBoxButtons.OKCancel,
                    MessageBoxIcon.Question);

                if (result == System.Windows.Forms.DialogResult.OK)
                {
                    bool UserisAdmin = false;

                    //Wenn Benutzer Admin ist dann Zweites Mal nachfragen
                    MySqlConnection connection = new MySqlConnection(myConnectionStringGlobal);
                    MySqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT admin FROM dbbv_users WHERE ID="
                        + tbox_UserEdit_ID.Text;

                    MySqlDataReader Reader;
                    connection.Open();
                    Reader = command.ExecuteReader();
                    while(Reader.Read())
                    {
                        if (Reader.GetValue(0).ToString().ToLower() == "true")
                        {
                            UserisAdmin = true;
                        }
                        else
                        {
                            UserisAdmin = false;
                        }
                    }

                    if (UserisAdmin)
                    {
                        DialogResult result2 = MessageBox.Show("Dieser Benutzer ist ebenfalls Administrator!\n" +
                            "\nMöchten Sie diesen Benutzer wirklich löschen?" +
                            "\nBenutzername: " + tbox_UserEdit_username.Text,
                            "Löschbestätigung",
                            MessageBoxButtons.OKCancel,
                            MessageBoxIcon.Question);

                        if (result2 == System.Windows.Forms.DialogResult.OK)
                        {
                            deleteUser(Convert.ToInt32(tbox_UserEdit_ID.Text));
                        }
                    }
                    else
                    {
                        deleteUser(Convert.ToInt32(tbox_UserEdit_ID.Text));
                    }
                }
            }
            
        }

        private void btn_UserEdit_Save_Click(object sender, EventArgs e)
        {
            //Standard Prozedur zum Speichern bei Änderungen
            if (tbox_UserEdit_Nachname.Text == lbl_UserEdit_OldNachname.Text &&
                tbox_UserEdit_Vorname.Text == lbl_UserEdit_OldVorname.Text)
            {
                MessageBox.Show("Sie haben keine Änderungen vorgenommen!",
                    "Information!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Warning);
            }
            else
            {
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myInsertQuery = "UPDATE dbbv_users SET user_firstname='" + tbox_UserEdit_Vorname.Text +
                    "', user_surname='" + tbox_UserEdit_Nachname.Text + "' WHERE ID = " +
                    tbox_UserEdit_ID.Text;
                MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                MessageBox.Show("Bei Benutzer: " + tbox_UserEdit_username.Text +
                    "\n\nVorname in : " + tbox_UserEdit_Vorname.Text + " und" +
                    "\nNachname in : " + tbox_UserEdit_Nachname.Text + " geändert!",
                    "Speicherbestätigung!",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);

                pnl_Home.BringToFront();
            }
        }

        private void tbox_newUser_Nachname_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                CreateUsername();
            }
        }

        private void tbox_newUser_Vorname_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                CreateUsername();
            }
        }

        private void btn_createUsername_Click(object sender, EventArgs e)
        {
            CreateUsername();
        }

        private void btn_newUser_Cancel_Click(object sender, EventArgs e)
        {
            pnl_usersAdmin_Super.BringToFront();
            pnl_usersAdmin_Main.Focus();
        }

        //Legt den Benutzer mit angegebenen Werten + Default Werten an
        private void btn_newUser_Save_Click(object sender, EventArgs e)
        {
            //Bestätigungsfrage
            DialogResult result = MessageBox.Show("Möchten Sie diesen Benutzer anlegen? \n\n" +
                "Benutzername: " + tbox_newUser_Username.Text +
                "\nVorname     : " + tbox_newUser_Vorname.Text +
                "\nNachname    : " + tbox_newUser_Nachname.Text,
                "Bestätigung",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question);

            if (result == System.Windows.Forms.DialogResult.OK)
            {
                //Hash für PW 123 erstellen
                string DefaultPW = "123" + salt + tbox_newUser_Username.Text;
                System.Security.Cryptography.SHA512 shaM = System.Security.Cryptography.SHA512.Create();

                DefaultPW = BitConverter.ToString(
                            shaM.ComputeHash(
                            Encoding.Unicode.GetBytes((
                            DefaultPW)))).Replace("-", "").ToLower();


                //User anlegen
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myInsertQuery = "INSERT INTO dbbv_users (username,user_surname,user_firstname,password,admin) " +
                    " Values('" + tbox_newUser_Username.Text + "','" +
                    tbox_newUser_Nachname.Text + "','" +
                    tbox_newUser_Vorname.Text + "','" +
                    DefaultPW + "'," +
                    "0)";
                MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                //Bestätigung des Anlegens und Mitteilung, dass PW 123 ist
                MessageBox.Show("Benutzer: " + tbox_newUser_Username.Text + " angelegt!\n\n" +
                "Das Passwort ist: 123 \n\n" +
                "Bitte merken Sie sich das Passwort und teilen es dem Benutzer mit!",
                        "Speicherbestätigung!",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);

                //Zurück auf Home
                pnl_Home.BringToFront();
            }
        }
        

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            CheckUnallowedChar_TextBox(sender);
        }

        private void richTextBox1_TextChanged(object sender, EventArgs e)
        {
            CheckUnallowedChar_RichTextBox(sender);
        }

        private void btn_resetPW_Click(object sender, EventArgs e)
        {
            //Bestätigungsfrage
            DialogResult result = MessageBox.Show("Möchten Sie diesem Benutzer das Passwort"+
                " wirklich zurücksetzen?"+
                "\n\nBenutzername: " + tbox_UserEdit_username.Text+
                "\nVorname: " + tbox_UserEdit_Vorname.Text+
                "\nNachname : " + tbox_UserEdit_Nachname.Text,
                "Bestätigung",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question);

            if (result == System.Windows.Forms.DialogResult.OK)
            {
                //PW salzen, hashen und in DB speichern
                string newpw = "123" + salt + tbox_UserEdit_username.Text;
                System.Security.Cryptography.SHA512 shaM = System.Security.Cryptography.SHA512.Create();

                newpw = BitConverter.ToString(
                            shaM.ComputeHash(
                            Encoding.Unicode.GetBytes((
                            newpw)))).Replace("-", "").ToLower();

                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myInsertQuery = "UPDATE dbbv_users SET password = '" +
                    newpw + "' WHERE username = '" + tbox_UserEdit_username.Text + "'";
                MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                //Bestätigungsmeldung über erfolgreichen PWChange
                MessageBox.Show("Das Passwort des Benutzers: "+
                    "\n\nBenutzername: " + tbox_UserEdit_username.Text+
                    "\nVorname: " + tbox_UserEdit_Vorname.Text+
                    "\nNachname : " + tbox_UserEdit_Nachname.Text+
                    "\n\nwurde auf 123 zurückgesetzt!"+
                    "\nTeilen Sie Ihm dies bitte mit!",
                    "Bestätigung",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);

                //Auf Hauptpanel leiten
                pnl_usersAdmin_Super.BringToFront();
                pnl_usersAdmin_Main.Focus();
            }
        }

        private void pnl_login_Enter(object sender, EventArgs e)
        {
            tbox_username.Focus();
        }

        private void Form1_Shown(object sender, EventArgs e)
        {
            tbox_username.Focus();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            startLocation();
        }

        private void btn_change_location_bookdetail_Click(object sender, EventArgs e)
        {
            //Inhalt der Textboxen und der Dropbox zuweisen
            lbl_changeLocationBookID.Text = tbox_book_ID.Text;

            tbox_changeLocation_bookname.Text = tbox_book_name.Text;
            tbox_changeLocation_LabelID.Text = tbox_book_labelID.Text;


            MySqlConnection con2 = new MySqlConnection(myConnectionStringGlobal);
            MySqlCommand command = con2.CreateCommand();
            command.CommandText = "SELECT Ort FROM dbbv_ort";
            MySqlDataReader Reader;
            con2.Open();
            Reader = command.ExecuteReader();
            drop_changeLocation_newLocation.Items.Clear();

            while (Reader.Read())
            {
                drop_changeLocation_newLocation.Items.Add(Reader.GetValue(0).ToString());
            }
            con2.Close();


            //Panel nach vorne und Dropbox in Fokus stellen
            pnl_changeLocation.BringToFront();
            drop_changeLocation_newLocation.Focus();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            pnl_bookDetail.BringToFront();
            pnl_bookDetail.Focus();
        }



        private void btn_changeLocation_Save_Click(object sender, EventArgs e)
        {
            //Bestätigungsfrage
            DialogResult result = MessageBox.Show("Möchten Sie das Buch wirklich umziehen?"+
                "\n\nBuchname: " + tbox_changeLocation_bookname.Text +
                "\nBuchLabel: " + tbox_changeLocation_LabelID.Text +
                "\nNeuer Aufbewahrungsort : " + drop_changeLocation_newLocation.Text,
                "Bestätigung",
                MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question);

            if (result == System.Windows.Forms.DialogResult.OK)
            {
                //Id des neuen Ortes aus DB holen
                string ChangeLocation_newOrtID = "Text";

                MySqlConnection con2 = new MySqlConnection(myConnectionStringGlobal);
                MySqlCommand command = con2.CreateCommand();
                command.CommandText = "SELECT ID FROM dbbv_ort WHERE Ort = '" + 
                    drop_changeLocation_newLocation.Text + "'";
                MySqlDataReader Reader;
                con2.Open();
                Reader = command.ExecuteReader();
                cbox_book_location_new.Items.Clear();

                while (Reader.Read())
                {
                    ChangeLocation_newOrtID = (Reader.GetValue(0).ToString());
                }
                con2.Close();

                //Updaten des Buches
                MySqlConnection myConnection = new MySqlConnection(myConnectionStringGlobal);
                string myInsertQuery = "UPDATE dbbv_books SET location = " +
                    ChangeLocation_newOrtID + " WHERE ID = '" + lbl_changeLocationBookID.Text + "'";
                MySqlCommand myCommand = new MySqlCommand(myInsertQuery);
                myCommand.Connection = myConnection;
                myConnection.Open();
                myCommand.ExecuteNonQuery();
                myCommand.Connection.Close();

                //Bestätigungsmeldung über erfolgreichen PWChange
                MessageBox.Show("Das Buch: " + tbox_changeLocation_bookname.Text +
                    " steht nun an ORT: " + drop_changeLocation_newLocation.Text,
                    "Bestätigung",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);

                //Detailinfo neu laden
                showbookdetail(lbl_changeLocationBookID.Text);
            }
        }




    }
}
